{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas加载数据 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### csv数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Python</th>\n",
       "      <th>Qianfeng</th>\n",
       "      <th>Java</th>\n",
       "      <th>NumPy</th>\n",
       "      <th>Pandas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>32</td>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31</td>\n",
       "      <td>27</td>\n",
       "      <td>3</td>\n",
       "      <td>37</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>43</td>\n",
       "      <td>40</td>\n",
       "      <td>15</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48</td>\n",
       "      <td>39</td>\n",
       "      <td>4</td>\n",
       "      <td>43</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36</td>\n",
       "      <td>10</td>\n",
       "      <td>34</td>\n",
       "      <td>5</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "      <td>23</td>\n",
       "      <td>42</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>29</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>21</td>\n",
       "      <td>20</td>\n",
       "      <td>30</td>\n",
       "      <td>47</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>13</td>\n",
       "      <td>25</td>\n",
       "      <td>29</td>\n",
       "      <td>38</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>45</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>33</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Python  Qianfeng  Java  NumPy  Pandas\n",
       "0      13        43    32     34      35\n",
       "1      31        27     3     37      29\n",
       "2      10        43    40     15      48\n",
       "3      48        39     4     43      40\n",
       "4      36        10    34      5      28\n",
       "5      16        10    23     42       9\n",
       "6      29         4    12      0      45\n",
       "7      21        20    30     47      21\n",
       "8      13        25    29     38       2\n",
       "9      45         9     5     33      22"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = np.random.randint(0, 50, size=(10, 5))\n",
    "df = pd.DataFrame(data=data, columns=['Python', 'Qianfeng', 'Java', 'NumPy', 'Pandas'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- df.to_csv: 保存到csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sep: 分隔符，默认是逗号\n",
    "# header: 是否保存列索引\n",
    "# index: 是否保留行索引\n",
    "df.to_csv('data.csv', sep=',', header=True, index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- df.read_csv: 加载csv数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Python</th>\n",
       "      <th>Qianfeng</th>\n",
       "      <th>Java</th>\n",
       "      <th>NumPy</th>\n",
       "      <th>Pandas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>32</td>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31</td>\n",
       "      <td>27</td>\n",
       "      <td>3</td>\n",
       "      <td>37</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>43</td>\n",
       "      <td>40</td>\n",
       "      <td>15</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48</td>\n",
       "      <td>39</td>\n",
       "      <td>4</td>\n",
       "      <td>43</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36</td>\n",
       "      <td>10</td>\n",
       "      <td>34</td>\n",
       "      <td>5</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "      <td>23</td>\n",
       "      <td>42</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>29</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>21</td>\n",
       "      <td>20</td>\n",
       "      <td>30</td>\n",
       "      <td>47</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>13</td>\n",
       "      <td>25</td>\n",
       "      <td>29</td>\n",
       "      <td>38</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>45</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>33</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Python  Qianfeng  Java  NumPy  Pandas\n",
       "0      13        43    32     34      35\n",
       "1      31        27     3     37      29\n",
       "2      10        43    40     15      48\n",
       "3      48        39     4     43      40\n",
       "4      36        10    34      5      28\n",
       "5      16        10    23     42       9\n",
       "6      29         4    12      0      45\n",
       "7      21        20    30     47      21\n",
       "8      13        25    29     38       2\n",
       "9      45         9     5     33      22"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data.csv', sep=',', header=[0], index_col=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "不获取列: header=None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>Python</td>\n",
       "      <td>Qianfeng</td>\n",
       "      <td>Java</td>\n",
       "      <td>NumPy</td>\n",
       "      <td>Pandas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0.0</th>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>32</td>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>31</td>\n",
       "      <td>27</td>\n",
       "      <td>3</td>\n",
       "      <td>37</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.0</th>\n",
       "      <td>10</td>\n",
       "      <td>43</td>\n",
       "      <td>40</td>\n",
       "      <td>15</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3.0</th>\n",
       "      <td>48</td>\n",
       "      <td>39</td>\n",
       "      <td>4</td>\n",
       "      <td>43</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4.0</th>\n",
       "      <td>36</td>\n",
       "      <td>10</td>\n",
       "      <td>34</td>\n",
       "      <td>5</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5.0</th>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "      <td>23</td>\n",
       "      <td>42</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6.0</th>\n",
       "      <td>29</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7.0</th>\n",
       "      <td>21</td>\n",
       "      <td>20</td>\n",
       "      <td>30</td>\n",
       "      <td>47</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8.0</th>\n",
       "      <td>13</td>\n",
       "      <td>25</td>\n",
       "      <td>29</td>\n",
       "      <td>38</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9.0</th>\n",
       "      <td>45</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>33</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          1         2     3      4       5\n",
       "0                                         \n",
       "NaN  Python  Qianfeng  Java  NumPy  Pandas\n",
       "0.0      13        43    32     34      35\n",
       "1.0      31        27     3     37      29\n",
       "2.0      10        43    40     15      48\n",
       "3.0      48        39     4     43      40\n",
       "4.0      36        10    34      5      28\n",
       "5.0      16        10    23     42       9\n",
       "6.0      29         4    12      0      45\n",
       "7.0      21        20    30     47      21\n",
       "8.0      13        25    29     38       2\n",
       "9.0      45         9     5     33      22"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data.csv', sep=',', header=None, index_col=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- pd.read_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Python</th>\n",
       "      <th>Qianfeng</th>\n",
       "      <th>Java</th>\n",
       "      <th>NumPy</th>\n",
       "      <th>Pandas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>32</td>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31</td>\n",
       "      <td>27</td>\n",
       "      <td>3</td>\n",
       "      <td>37</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>43</td>\n",
       "      <td>40</td>\n",
       "      <td>15</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48</td>\n",
       "      <td>39</td>\n",
       "      <td>4</td>\n",
       "      <td>43</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36</td>\n",
       "      <td>10</td>\n",
       "      <td>34</td>\n",
       "      <td>5</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "      <td>23</td>\n",
       "      <td>42</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>29</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>21</td>\n",
       "      <td>20</td>\n",
       "      <td>30</td>\n",
       "      <td>47</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>13</td>\n",
       "      <td>25</td>\n",
       "      <td>29</td>\n",
       "      <td>38</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>45</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>33</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Python  Qianfeng  Java  NumPy  Pandas\n",
       "0      13        43    32     34      35\n",
       "1      31        27     3     37      29\n",
       "2      10        43    40     15      48\n",
       "3      48        39     4     43      40\n",
       "4      36        10    34      5      28\n",
       "5      16        10    23     42       9\n",
       "6      29         4    12      0      45\n",
       "7      21        20    30     47      21\n",
       "8      13        25    29     38       2\n",
       "9      45         9     5     33      22"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read_table :\n",
    "#    默认分隔符sep='\\t'\n",
    "pd.read_table('data.csv', sep=',', index_col=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### excel数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Python</th>\n",
       "      <th>Qianfeng</th>\n",
       "      <th>Java</th>\n",
       "      <th>NumPy</th>\n",
       "      <th>Pandas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>44</td>\n",
       "      <td>23</td>\n",
       "      <td>9</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>26</td>\n",
       "      <td>41</td>\n",
       "      <td>7</td>\n",
       "      <td>31</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>17</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>17</td>\n",
       "      <td>49</td>\n",
       "      <td>31</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>19</td>\n",
       "      <td>15</td>\n",
       "      <td>42</td>\n",
       "      <td>48</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>32</td>\n",
       "      <td>36</td>\n",
       "      <td>22</td>\n",
       "      <td>44</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>38</td>\n",
       "      <td>40</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>23</td>\n",
       "      <td>19</td>\n",
       "      <td>38</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Python  Qianfeng  Java  NumPy  Pandas\n",
       "0       3        44    23      9      45\n",
       "1      26        41     7     31       7\n",
       "2       3         6     7      1      16\n",
       "3      40         0    32     16      17\n",
       "4      10        17    21     22       5\n",
       "5      17        49    31     20       7\n",
       "6      19        15    42     48       4\n",
       "7      32        36    22     44      17\n",
       "8      38        40     1     13      30\n",
       "9       2        23    19     38      33"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = np.random.randint(0, 50, size=(10, 5))\n",
    "df = pd.DataFrame(data=data, columns=['Python', 'Qianfeng', 'Java', 'NumPy', 'Pandas'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- df1.to_excel: 保存到excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sheet_name： 工作表名称\n",
    "# header: 是否保存列索引\n",
    "# index: 是否保存行索引\n",
    "df.to_excel('data.xlsx', sheet_name='Sheet1', header=True, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- df1.read_excel: 读取excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>44</td>\n",
       "      <td>23</td>\n",
       "      <td>9</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>26</td>\n",
       "      <td>41</td>\n",
       "      <td>7</td>\n",
       "      <td>31</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>17</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>17</td>\n",
       "      <td>49</td>\n",
       "      <td>31</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>19</td>\n",
       "      <td>15</td>\n",
       "      <td>42</td>\n",
       "      <td>48</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>32</td>\n",
       "      <td>36</td>\n",
       "      <td>22</td>\n",
       "      <td>44</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>38</td>\n",
       "      <td>40</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>23</td>\n",
       "      <td>19</td>\n",
       "      <td>38</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C   D   E\n",
       "0   3  44  23   9  45\n",
       "1  26  41   7  31   7\n",
       "2   3   6   7   1  16\n",
       "3  40   0  32  16  17\n",
       "4  10  17  21  22   5\n",
       "5  17  49  31  20   7\n",
       "6  19  15  42  48   4\n",
       "7  32  36  22  44  17\n",
       "8  38  40   1  13  30\n",
       "9   2  23  19  38  33"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel('data.xlsx', sheet_name='Sheet1', header=[0, 1])\n",
    "\n",
    "# sheet_name=0： 读取第1个工作表\n",
    "# names : 替换原来的列名\n",
    "pd.read_excel('data.xlsx', sheet_name=0, header=0, names=list('ABCDE'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MySQL数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "需要安装pymysql\n",
    "\n",
    "- pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple\n",
      "Collecting pymysql\n",
      "  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/0c/94/e4181a1f6286f545507528c78016e00065ea913276888db2262507693ce5/PyMySQL-1.1.1-py3-none-any.whl (44 kB)\n",
      "Installing collected packages: pymysql\n",
      "Successfully installed pymysql-1.1.1\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "需要安装sqlalchemy: \n",
    "\n",
    "- pip install sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple\n",
    "- sqlalchemy是Python语言下的数据库引擎库\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple\n",
      "Collecting sqlalchemy\n",
      "  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/e8/86/989f4b4c47da0d9b152465f6623b6a6415179b4e6bb967f08199bdad98eb/SQLAlchemy-2.0.32-cp310-cp310-win_amd64.whl (2.1 MB)\n",
      "     ---------------------------------------- 0.0/2.1 MB ? eta -:--:--\n",
      "     ---------------------------------------- 2.1/2.1 MB 11.7 MB/s eta 0:00:00\n",
      "Requirement already satisfied: typing-extensions>=4.6.0 in d:\\developer\\wgj\\vscode_workspace\\python\\pyecharts\\.venv\\lib\\site-packages (from sqlalchemy) (4.12.2)\n",
      "Collecting greenlet!=0.4.17 (from sqlalchemy)\n",
      "  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/a6/76/e1ee9f290bb0d46b09704c2fb0e609cae329eb308ad404c0ee6fa1ecb8a5/greenlet-3.0.3-cp310-cp310-win_amd64.whl (292 kB)\n",
      "Installing collected packages: greenlet, sqlalchemy\n",
      "Successfully installed greenlet-3.0.3 sqlalchemy-2.0.32\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Python</th>\n",
       "      <th>Pandas</th>\n",
       "      <th>PyTorch</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>88</td>\n",
       "      <td>84</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51</td>\n",
       "      <td>24</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>51</td>\n",
       "      <td>127</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>57</td>\n",
       "      <td>10</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Python  Pandas  PyTorch\n",
       "0      88      84      101\n",
       "1      51      24       57\n",
       "2      51     127       20\n",
       "3      57      10       68\n",
       "4       0      28       84"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 创建数据\n",
    "data = np.random.randint(0, 150, size=(150, 3))\n",
    "df = pd.DataFrame(data=data, columns=['Python', 'Pandas', 'PyTorch'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 先连接MySQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mysql+pymysql ：数据库类型+驱动\n",
    "# root:root ：数据库用户名和密码\n",
    "# localhost:3306 ：数据库地址和MySQL端口\n",
    "# db: 数据库名\n",
    "conn = create_engine('mysql+pymysql://root:123456@localhost:3306/db')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- df.to_sql保存到MySQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "150"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_sql(\n",
    "    name='score',  # 数据库中表名字\n",
    "    con=conn,  # 数据库连接对象\n",
    "    index=False,  # 是否保存行索引\n",
    "    if_exists='append'  # 如果表存在，则追加数据\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- pd.read_sql: 从MySQL中加载数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Python</th>\n",
       "      <th>Pandas</th>\n",
       "      <th>PyTorch</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>88</td>\n",
       "      <td>84</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51</td>\n",
       "      <td>24</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>51</td>\n",
       "      <td>127</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>57</td>\n",
       "      <td>10</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>59</td>\n",
       "      <td>91</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>114</td>\n",
       "      <td>42</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>115</td>\n",
       "      <td>108</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>5</td>\n",
       "      <td>46</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>141</td>\n",
       "      <td>84</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>150 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Python  Pandas  PyTorch\n",
       "0        88      84      101\n",
       "1        51      24       57\n",
       "2        51     127       20\n",
       "3        57      10       68\n",
       "4         0      28       84\n",
       "..      ...     ...      ...\n",
       "145      59      91       82\n",
       "146     114      42       51\n",
       "147     115     108      140\n",
       "148       5      46       34\n",
       "149     141      84       71\n",
       "\n",
       "[150 rows x 3 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    sql='select * from score',  # sql语句\n",
    "    con=conn,   # 数据库连接对象\n",
    "#     index_col='Python'  # 指定行索引的列名\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
